package com.tencent.qplus.paddata.stat2;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DecimalFormat;
import java.util.HashMap;
import java.util.Map;
import java.util.Set;

import com.tencent.qplus.paddata.db.DataSourceManager;
import com.tencent.qplus.paddata.stat.Job;
import com.tencent.qplus.paddata.util.JdbcUtils;

/**
 * 留存率计算
 * @author shiangzhang
 *
 * 2012-1-10
 */
public class StatRetentionRateJob extends Job {
	
	private DecimalFormat dfc = new DecimalFormat("0.00%");
	
	
	private Map<String,RetentionRate> getAllNewUin(String objStatDate) throws SQLException{
		String sql = "select uin,loginVersion from padversiondata.newuin where firstLoginDate='"+objStatDate+"'";
		Map<String,RetentionRate> newUinRetentionMap = new HashMap<String,RetentionRate>();
		Statement st = null;
		ResultSet rs = null;
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		try {
			st = conn.createStatement();
			rs = st.executeQuery(sql);
			while(rs.next()){
				String version = rs.getString("loginVersion");
				String uin = rs.getString("uin");
				RetentionRate  rr = newUinRetentionMap.get(version);
				if(rr==null){
					rr = new RetentionRate();
					newUinRetentionMap.put(version, rr);
				}
				rr.newUinList.add(uin);
			}
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(rs);
			JdbcUtils.release(st);
			JdbcUtils.release(conn);
		}
		return newUinRetentionMap;
	}
	/**
	 * 计算次日留存率
	 * @throws SQLException 
	 */
	private void statNextDayRetentionRate() throws SQLException{
		String pre2DaysStr = dateUtils.getDateStrByDaysFromDate(statDate, -1);
		
		Map<String,RetentionRate> newUinRetentionMap = getAllNewUin(pre2DaysStr);
		Set<String> keySet = newUinRetentionMap.keySet();
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		Connection statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst = null;
		try {
			String resultSql = "REPLACE INTO retentionratestat(day,totalNewUV,retention,loginVersion,periodLength,retentionRate,defectionRate) VALUES(?,?,?,?,1,?,?);";
			st = conn.createStatement();
			pst = statResultConn.prepareStatement(resultSql);
			int allVerionTotalNewUin = 0;
			int allVersionNextDayLogin = 0;
			for(String version:keySet){
				RetentionRate rr = newUinRetentionMap.get(version);
				for(String uin:rr.newUinList){
					String sql = "select * from uinlogin_"+ (Long.valueOf(uin)%100)+" where uin='"+uin+"' and logindate='" + statDate + "' limit 1";
					try {
						rs = st.executeQuery(sql);
						if(rs.next()){
							rr.nextDayLogin ++;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}finally{
						JdbcUtils.release(rs);
					}
				}
				allVerionTotalNewUin +=  rr.newUinList.size();
				allVersionNextDayLogin += rr.nextDayLogin;
				pst.setString(1, pre2DaysStr);
				pst.setInt(2, rr.newUinList.size());
				pst.setInt(3, rr.nextDayLogin);
				pst.setString(4, version);
				float retentionRate = rr.nextDayLogin/(float)rr.newUinList.size();
				pst.setString(5, dfc.format(retentionRate));
				pst.setString(6, dfc.format(1-retentionRate));
				pst.addBatch();
			}
			
			pst.setString(1, pre2DaysStr);
			pst.setInt(2, allVerionTotalNewUin);
			pst.setInt(3, allVersionNextDayLogin);
			pst.setString(4, "");
			
			float retentionRate = allVersionNextDayLogin/(float)allVerionTotalNewUin;
			pst.setString(5, dfc.format(retentionRate));
			pst.setString(6, dfc.format(1-retentionRate));
			
			pst.addBatch();
			pst.executeBatch();
			
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(st);
			JdbcUtils.release(pst);
			JdbcUtils.release(conn);
			JdbcUtils.release(statResultConn);
		}
	}

	/**
	 *计算周留存率 
	 * @throws SQLException 
	 */
	private void statWeekRetentionRate() throws SQLException{
		

		String pre8DaysStr = dateUtils.getDateStrByDaysFromDate(statDate, -7);
		
		Map<String,RetentionRate> newUinRetentionMap = getAllNewUin(pre8DaysStr);
		
		
		Set<String> keySet = newUinRetentionMap.keySet();
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		Connection statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst = null;
		try {
			String resultSql = "REPLACE INTO retentionratestat(day,totalNewUV,retention,loginVersion,periodLength,retentionRate,defectionRate) VALUES(?,?,?,?,7,?,?);";
			st = conn.createStatement();
			pst = statResultConn.prepareStatement(resultSql);
			int allVerionTotalNewUin = 0;
			int allVersionWeekLogin = 0;
			for(String version:keySet){
				RetentionRate rr = newUinRetentionMap.get(version);
				for(String uin:rr.newUinList){
					String sql = "select * from uinlogin_"+ (Long.valueOf(uin)%100)+" where uin='"+uin+"' and logindate>'"+pre8DaysStr+"' and logindate<='" + statDate + "' limit 1";
					
					try {
						rs = st.executeQuery(sql);
						if(rs.next()){
							rr.weekLogin ++;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}finally{
						JdbcUtils.release(rs);
					}
				}
				allVerionTotalNewUin +=  rr.newUinList.size();
				allVersionWeekLogin += rr.weekLogin;
				pst.setString(1, pre8DaysStr);
				pst.setInt(2, rr.newUinList.size());
				pst.setInt(3, rr.weekLogin);
				pst.setString(4, version);
				
				float retentionRate = rr.weekLogin/(float)rr.newUinList.size();
				pst.setString(5, dfc.format(retentionRate));
				pst.setString(6, dfc.format(1-retentionRate));
				
				pst.addBatch();
			}
			
			pst.setString(1, pre8DaysStr);
			pst.setInt(2, allVerionTotalNewUin);
			pst.setInt(3, allVersionWeekLogin);
			pst.setString(4, "");
			
			float retentionRate = allVersionWeekLogin/(float)allVerionTotalNewUin;
			pst.setString(5, dfc.format(retentionRate));
			pst.setString(6, dfc.format(1-retentionRate));
			
			pst.addBatch();
			pst.executeBatch();
			
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(st);
			JdbcUtils.release(pst);
			JdbcUtils.release(conn);
			JdbcUtils.release(statResultConn);
		}
	
		
	}
	
	/**
	 * 计算双周留存率
	 * @throws SQLException 
	 */
	private void statTwoWeekRetentionRate() throws SQLException{

		String pre15DaysStr = dateUtils.getDateStrByDaysFromDate(statDate, -14);
		
		Map<String,RetentionRate> newUinRetentionMap = getAllNewUin(pre15DaysStr);
		
		Set<String> keySet = newUinRetentionMap.keySet();
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		Connection statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst = null;
		try {
			String resultSql = "REPLACE INTO retentionratestat(day,totalNewUV,retention,loginVersion,periodLength,retentionRate,defectionRate) VALUES(?,?,?,?,14,?,?);";
			st = conn.createStatement();
			pst = statResultConn.prepareStatement(resultSql);
			int allVerionTotalNewUin = 0;
			int allVersionTwoWeeksLogin = 0;
			for(String version:keySet){
				RetentionRate rr = newUinRetentionMap.get(version);
				for(String uin:rr.newUinList){
					String sql = "select * from uinlogin_"+ (Long.valueOf(uin)%100)+" where uin='"+uin+"' and logindate>'"+pre15DaysStr+"' and logindate<='" + statDate + "' limit 1";
					
					try {
						rs = st.executeQuery(sql);
						if(rs.next()){
							rr.towWeekLogin ++;
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}finally{
						JdbcUtils.release(rs);
					}
				}
				allVerionTotalNewUin +=  rr.newUinList.size();
				allVersionTwoWeeksLogin += rr.towWeekLogin;
				pst.setString(1, pre15DaysStr);
				pst.setInt(2, rr.newUinList.size());
				pst.setInt(3, rr.towWeekLogin);
				pst.setString(4, version);
				
				float retentionRate = rr.towWeekLogin/(float)rr.newUinList.size();
				pst.setString(5, dfc.format(retentionRate));
				pst.setString(6, dfc.format(1-retentionRate));
				
				pst.addBatch();
			}
			
			pst.setString(1, pre15DaysStr);
			pst.setInt(2, allVerionTotalNewUin);
			pst.setInt(3, allVersionTwoWeeksLogin);
			pst.setString(4, "");
			float retentionRate = allVersionTwoWeeksLogin/(float)allVerionTotalNewUin;
			pst.setString(5, dfc.format(retentionRate));
			pst.setString(6, dfc.format(1-retentionRate));

			pst.addBatch();
			pst.executeBatch();
			
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(st);
			JdbcUtils.release(pst);
			JdbcUtils.release(conn);
			JdbcUtils.release(statResultConn);
		}
	
		
	
	}
	
	/**
	 * 计算月留存率
	 * @throws SQLException 
	 */
	private void statMonthRetentionRate() throws SQLException{
		String pre32DaysStr = dateUtils.getDateStrByDaysFromDate(statDate, -31);
		Map<String,RetentionRate> newUinRetentionMap = getAllNewUin(pre32DaysStr);
		
		Set<String> keySet = newUinRetentionMap.keySet();
		Connection conn = DataSourceManager.getInstance().getDataSource("padversiondatadb").getConnection();
		Connection statResultConn = DataSourceManager.getInstance().getDataSource("padversiondatastatdb").getConnection();
		Statement st = null;
		ResultSet rs = null;
		PreparedStatement pst = null;
		try {
			String resultSql = "REPLACE INTO retentionratestat(day,totalNewUV,retention,loginVersion,periodLength,retentionRate,defectionRate) VALUES(?,?,?,?,31,?,?);";
			st = conn.createStatement();
			pst = statResultConn.prepareStatement(resultSql);
			int allVerionTotalNewUin = 0;
			int allVersionMonthLogin = 0;
			for(String version:keySet){
				RetentionRate rr = newUinRetentionMap.get(version);
				for(String uin:rr.newUinList){
					String sql = "select * from uinlogin_"+ (Long.valueOf(uin)%100)+" where uin='"+uin+"' and logindate>'"+pre32DaysStr+"' and logindate<='" + statDate + "' limit 1";
					try {
						rs = st.executeQuery(sql);
						if(rs.next()){
							rr.monthLogin ++;
						}
					} catch (SQLException e) {
						logger.error(e);
					}finally{
						JdbcUtils.release(rs);
					}
				}
				allVerionTotalNewUin +=  rr.newUinList.size();
				allVersionMonthLogin += rr.monthLogin;
				pst.setString(1, pre32DaysStr);
				pst.setInt(2, rr.newUinList.size());
				pst.setInt(3, rr.monthLogin);
				pst.setString(4, version);
				
				float retentionRate = rr.monthLogin/(float)rr.newUinList.size();
				pst.setString(5, dfc.format(retentionRate));
				pst.setString(6, dfc.format(1-retentionRate));
				
				pst.addBatch();
			}
			//所有的版本
			pst.setString(1, pre32DaysStr);
			pst.setInt(2, allVerionTotalNewUin);
			pst.setInt(3, allVersionMonthLogin);
			pst.setString(4, "");
			
			float retentionRate = allVersionMonthLogin/(float)allVerionTotalNewUin;
			pst.setString(5, dfc.format(retentionRate));
			pst.setString(6, dfc.format(1-retentionRate));
			
			pst.addBatch();
			pst.executeBatch();
			
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}finally{
			JdbcUtils.release(st);
			JdbcUtils.release(pst);
			JdbcUtils.release(conn);
			JdbcUtils.release(statResultConn);
		}
	}
	
	@Override
	public void run() throws SQLException {
		 try {
			
			statNextDayRetentionRate();
			
			statWeekRetentionRate();
			
			statTwoWeekRetentionRate();
			
			statMonthRetentionRate();
			
		} catch (SQLException e) {
			logger.error(e);
			throw e;
		}

	}


	@Override
	public void init() {
		// TODO Auto-generated method stub

	}

	public static void main(String[] args){
		StatRetentionRateJob job = new StatRetentionRateJob();
		job.stat(args);
	}
}
